Skip to main content

Displaying Multiple Column Output

Overview​

JSON content data from modern databases frequently has multiple embedded arrays. For example, the sample MongoDB mFlix data has movie collection elements such as the following.

  

Notice the cast and writers fields are embedded arrays (lists). Meanwhile the awards and imdb values are embedded objects. Unlike legacy tools Qarbine handles both of these scenarios just fine with its powerful analytic data processing capabilities.

It may be desirable to include these two lists side by side in a report for better space usage rather than simply having one list above the other. Below is an example of the possible output.

  

The following example can be found at “example/MongoDB/mFlix/IMDB mFlix example page crossing”.

Converting JSON String Data into Objects​

The techniques described here apply to other databases which support JSON explicitly or via basic text (i.e., varchar) data types. For the latter, the stored JSON string can be converted to a JSON object using a template formula as shown below.

object = jsonParse(string)

This is done during the template processing stage.

JSON strings can also be converted into JSON objects at the Qarbine data source level using the Qarbine pragmas listed below. This conversion is done just after the Qarbine server receives the query’s answer set. The template processing stage is downstream from here.

Pragma Keyword Description
convertToObjectProvide a CSV list of fields to convert strings to JSON objects via JSON.parse(someString). This is done in-place.
convertToObjectsProvide a CSV list of ARRAY fields to convert their element strings to JSON objects via JSON.parse(someString). This is done in-place.

Template Design Steps​

The main data definition is shown below.

  

  

  

Note the setting of a variable “moviesElement” for the main elements.

Template “groups” are the mechanism to iterate through lists. The two embedded arrays (cast and writers) will be combined into a single list. Each element in the list has an element from each array. The first element thus has the first writer and the first cast member from their respective embedded arrays. When the lists are of different sizes then a null value is used to fill in the gaps.

In the movies template the first group (1.1) presents the main information of the movie. There is a 1.2 group which is responsible for the writers and cast members information. These are shown below.

  

To get the merged lists, access group 1.2 properties by selecting any of its lines and right clicking for the context menu.

  

  

The fields of interest are shown below.

  

  

The formula is

listFromLists(@moviesElement.writers, @moviesElement.cast)

During the 1.2 group looping itself, the @current references the current element of the combined arrays. The writer value is in the ‘elm1’ field and the cast member is in the ‘elm2’ field. These lists may have a different number of elements.

Since the values are simple strings, no special conditions are needed to handle the null filler values. If the writer value was an object looking like {first: “Bob”, last: “Smith”} then the @current.elm1 formula structure would be changed to use the 

IF (expression, trueValue, falseValue)

macro to check for the null filler value. A sample formula would then be

if (@current.elm1, @current.elm1.first, "")

You can also have multiple body lines defined and have each one use a processing condition. This is the easiest approach when the list elements are richer objects and not just simple values. The cases to handle would be

  • element 1 is not null and element 2 is not null,
  • element 1 is not null and element 2 is null, and
  • element 1 is null and element 2 is not null.

The line process conditions to control which body lines are activate would then be:

  • isNotNull(@current.elm1, @current.elm2)
  • and( isNotNull(@current.elm1), isNull( @current.elm2) )
  • and( isNull(@current.elm1), isNotNull(@current.elm2) )

Use the

  • first condition for line formatting elements from both lists,
  • second condition for line only formatting an element from the first list, and
  • third condition for line only formatting an element from the second list.